/**
 * @Package: 	com.group.DAO 
 * @author: 	Liu  
 * @date: 		Dec 28, 2018 9:47:47 PM 
 */
package com.group.DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.group.C3P0Utils.C3P0Utils;
import com.group.pojo.GClass;
import com.group.pojo.GGroup;
import com.group.pojo.GStudent;

/**
 * @ClassName:	 GStudentDAO.java
 * @Description: 该类的功能描述
 * @author:		 Liu
 * @date: 		 Dec 28, 2018 9:47:47 PM 
 */
public class GStudentDAO {
	/**
	 * @Function: 	 GStudentDAO.java
	 * @Description: 通过学生学号获取所在小组ID
	 * @param:		   学生学号
	 * @return:		  小组ID
	 * @author: 	 Liu
	 * @date:		 Dec 28, 2018 10:16:03 PM 
	 */
	public int getGroupIDByID(String studentID) {
		int groupID = 0;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "SELECT * FROM  g_student WHERE student_id = ?";
		try {
			conn = C3P0Utils.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, studentID);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				groupID = rs.getInt("group_id");
			}
		}catch (SQLException e) {
			e.printStackTrace();
		}finally{
			C3P0Utils.close(rs, pstmt, conn);
		}
		return groupID;
	}
	
	/**
	 * @Function: 	 GStudentDAO.java
	 * @Description: 更新学生姓名
	 * @param:		   学号，姓名
	 * @return:		 
	 * @author: 	 Liu
	 * @date:		 Dec 29, 2018 10:37:05 AM 
	 */
	public void updateStudentName(String studentID, String studentName) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "UPDATE g_student SET student_name = ? WHERE student_id = ?";
		try {
			conn = C3P0Utils.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, studentName);
			pstmt.setString(2, studentID);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			C3P0Utils.close(rs, pstmt, conn);
		}
	}
	
	/**
	 * @Function: 	 GStudentDAO.java
	 * @Description: 更新学生所在小组
	 * @param:		   小组ID，学生ID列表
	 * @return:		 
	 * @author: 	 Liu
	 * @date:		 Dec 29, 2018 8:28:03 PM 
	 */
	public void updateStudentGruopIDByID(int groupID, String[] studentID) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "UPDATE g_student SET group_id = ? WHERE student_id = ?";
		try {
			conn = C3P0Utils.getConnection();
			for(String stID:studentID) {
				pstmt = conn.prepareStatement(sql);
				pstmt.setInt(1, groupID);
				pstmt.setString(2, stID);
				pstmt.executeUpdate();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			C3P0Utils.close(rs, pstmt, conn);
		}
	}
	
	/**
	 * @Function: 	 GStudentDAO.java
	 * @Description: 获取学生信息
	 * @param:		   学生ID
	 * @return:		   学生信息
	 * @author: 	 Liu
	 * @date:		 Jan 3, 2019 9:12:03 PM 
	 */
	public GStudent getStudentInfoByID(String studentID) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		GStudent student = new GStudent();
		String sql = "SELECT * FROM g_student NATURAL JOIN g_class NATURAL JOIN g_group WHERE student_id = ?";
		try {
			conn = C3P0Utils.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, studentID);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				GClass gclass = new GClass(rs.getString("class_id"), rs.getString("class_name"), rs.getInt("class_year"));
				GGroup ggroup = new GGroup(rs.getInt("group_id"), rs.getString("group_name"));
				student.setStudentID(rs.getString("student_id"));
				student.setStudentName(rs.getString("student_name"));
				student.setGclass(gclass);
				student.setGgroup(ggroup);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			C3P0Utils.close(rs, pstmt, conn);
		}
		return student;
	}
}
